{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "a102e471",
   "metadata": {},
   "source": [
    "# Trade-Policy Dynamics: Evidence from 60 Years of U.S.-China Trade\n",
    "Alessandria,  Khan, Khederlarian, Ruhl, and Steinberg\n",
    "\n",
    "**inputs:**   inputs_calibration_sectors.dta</br> \n",
    "&emsp; &emsp; &emsp;sector_params.tex\n",
    "\n",
    "**outputs:** table1.tex</br> \n",
    "&emsp; &emsp; &ensp; &ensp;  table2.tex </br>\n",
    "&emsp; &emsp; &ensp; &ensp;  updates file \"numbers for the paper text.csv\"\n",
    "\n",
    "\n",
    "This code calculates the final versions of tables 1 and 2 in the manuscript. Table 3 is coded directly in the manuscript as all two three numbers are from other sources. The three numbers we calculate are in `numbers for the paper text.csv`.\n",
    "\n",
    "This code updates the file `numbers for the paper text.csv` which lists all the values reported in the manuscript text. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "fee2d2d0",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "dir_eint = '../20 Intermediate files/'\n",
    "dir_out = '../30 Final output files/'\n",
    "dir_outa = '../31 Final output files appendix/'\n",
    "\n",
    "# Load the df that holds the numbers in the text. It is created in `00 figures.ipynb` and is\n",
    "# added to in this notebook.\n",
    "text_nums = pd.read_csv(dir_out+'numbers for paper text.csv', index_col='pos', dtype={'pos':'str'})"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "de8c7cea",
   "metadata": {},
   "source": [
    "# Table 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "93336e2a",
   "metadata": {},
   "outputs": [],
   "source": [
    "firm_moments = pd.read_stata(dir_eint+'inputs_calibration_sectors.dta')\n",
    "firm_moments = firm_moments.drop('sector', axis=1)\n",
    "firm_moments = firm_moments.sort_values('sector_n')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "54227042",
   "metadata": {},
   "outputs": [],
   "source": [
    "descriptions = {1:'Food, beverage, tobacco',\n",
    "                2:'Textile, clothing, footwear',\n",
    "                3:'Wood and straw products',\n",
    "                4:'Paper, printing products',\n",
    "                5:'Energy products, chemicals',\n",
    "                6:'Rubber, plastic products',\n",
    "                7:'Non-metallic mineral products',\n",
    "                8:'Base metal manuf.',\n",
    "                9:'Calendered metal manuf.',\n",
    "                10:'Other machinery, equipment',\n",
    "                11:'Computer, electrical, optical',\n",
    "                12:'Electrical equipment manuf.',\n",
    "                13:'Vehicle manuf.',\n",
    "                14:'Furniture, other manuf.',\n",
    "                15:'Non-manufacturing'\n",
    "                }\n",
    "desc = pd.Series(data=descriptions, name='description')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "64e1695d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sector_n</th>\n",
       "      <th>description</th>\n",
       "      <th>export_partic_0407</th>\n",
       "      <th>exit_rate_0407</th>\n",
       "      <th>incumbent_premium_0407</th>\n",
       "      <th>cov_lexports_0407</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1</td>\n",
       "      <td>Food, beverage, tobacco</td>\n",
       "      <td>19</td>\n",
       "      <td>16</td>\n",
       "      <td>2.71</td>\n",
       "      <td>0.91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2</td>\n",
       "      <td>Textile, clothing, footwear</td>\n",
       "      <td>45</td>\n",
       "      <td>10</td>\n",
       "      <td>1.99</td>\n",
       "      <td>1.06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   sector_n                  description  export_partic_0407  exit_rate_0407  \\\n",
       "5         1      Food, beverage, tobacco                  19              16   \n",
       "12        2  Textile, clothing, footwear                  45              10   \n",
       "\n",
       "    incumbent_premium_0407  cov_lexports_0407  \n",
       "5                     2.71               0.91  \n",
       "12                    1.99               1.06  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Add descriptions to the data\n",
    "table1 = pd.merge(left=firm_moments, right=desc, left_on='sector_n', right_index=True, how='inner')\n",
    "\n",
    "# Reorder columns\n",
    "table1 = table1[['sector_n', 'description', 'export_partic_0407', 'exit_rate_0407', 'incumbent_premium_0407', 'cov_lexports_0407']]\n",
    "\n",
    "# Round\n",
    "for c, d, m, f  in zip(['export_partic_0407', 'exit_rate_0407', 'incumbent_premium_0407','cov_lexports_0407'],[0,0,2,2],[100,100,1,1],[int,int,float,float]):\n",
    "    table1[c] = (table1[c]*m).round(d).astype(f)\n",
    "\n",
    "table1['sector_n'] = table1['sector_n'].astype(int).astype(str)\n",
    "table1.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3627706c",
   "metadata": {},
   "source": [
    "## Write the table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "517be7c1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\\begin{tabular}{llcccc}\n",
      "\\toprule\n",
      "\\multicolumn{2}{c}{Sector} & \\makecell{Export\\\\part.} & \\makecell{Exit\\\\rate} & \\makecell{Incumbent\\\\size prem.} & \\makecell{CV log\\\\exports}\\\\\n",
      "\\midrule\n",
      "1 & Food, beverage, tobacco & 19 & 16 & 2.71 & 0.91 \\\\\n",
      "2 & Textile, clothing, footwear & 45 & 10 & 1.99 & 1.06 \\\\\n",
      "3 & Wood and straw products & 24 & 13 & 2.05 & 1.09 \\\\\n",
      "4 & Paper, printing products & 12 & 17 & 3.10 & 1.30 \\\\\n",
      "5 & Energy products, chemicals & 19 & 15 & 3.23 & 1.48 \\\\\n",
      "6 & Rubber, plastic products & 29 & 10 & 2.69 & 1.08 \\\\\n",
      "7 & Non-metallic mineral products & 16 & 18 & 2.26 & 0.85 \\\\\n",
      "8 & Base metal manuf. & 12 & 21 & 3.96 & 1.15 \\\\\n",
      "9 & Calendered metal manuf. & 29 & 10 & 2.48 & 1.24 \\\\\n",
      "10 & Other machinery, equipment & 23 & 13 & 3.33 & 1.54 \\\\\n",
      "11 & Computer, electrical, optical & 48 & 7 & 4.82 & 1.94 \\\\\n",
      "12 & Electrical equipment manuf. & 32 & 10 & 3.35 & 1.55 \\\\\n",
      "13 & Vehicle manuf. & 23 & 12 & 4.07 & 1.31 \\\\\n",
      "14 & Furniture, other manuf. & 59 & 7 & 1.76 & 0.95 \\\\\n",
      "15 & Non-manufacturing & 28 & 13 & 2.99 & 1.25 \\\\\n",
      "\\bottomrule\n",
      "\\addlinespace\n",
      "\\end{tabular}\n"
     ]
    }
   ],
   "source": [
    "t1_ltx = table1.to_latex(index=False, header=False, column_format='llcccc', float_format='%.2f')\n",
    "t1_l = t1_ltx.splitlines()\n",
    "t1_l.insert(2, '\\\\multicolumn{2}{c}{Sector} & \\\\makecell{Export\\\\\\\\part.} & \\\\makecell{Exit\\\\\\\\rate} & \\\\makecell{Incumbent\\\\\\\\size prem.} & \\\\makecell{CV log\\\\\\\\exports}\\\\\\\\')\n",
    "t1_l.insert(-1, '\\\\addlinespace')\n",
    "\n",
    "t1_ltx = '\\n'.join(t1_l)\n",
    "print(t1_ltx)\n",
    "\n",
    "with open(dir_out+'table1.tex', 'w') as f:\n",
    "    f.write(t1_ltx)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5d836548",
   "metadata": {},
   "source": [
    "### Numbers in the text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "76492308",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                                          text\n",
      "pos                                           \n",
      "4.1                   exports ranges from 0.85\n",
      "4.2                                    to 1.94\n",
      "4.3                 participation rate from 12\n",
      "4.4                             to 59 percent,\n",
      "4.5               the exit rate from 7 percent\n",
      "4.6                         to 21 percent, and\n",
      "4.7   incumbent premium from 1.76 percent, and\n",
      "4.8                                   to 4.82.\n",
      "4.9                     (correlation 0.51) but\n",
      "4.10                        exit rate (-0.55),\n",
      "4.11                       log exports (0.24),\n",
      "4.12                 incumbent premium (0.03).\n",
      "4.13                   which range from 2.7 to\n",
      "4.14                                   to 3.7.\n"
     ]
    }
   ],
   "source": [
    "\n",
    "text_nums.loc['4.1'] = f'exports ranges from {table1[\"cov_lexports_0407\"].min():.2f}'\n",
    "text_nums.loc['4.2'] = f'to {table1[\"cov_lexports_0407\"].max():.2f}'\n",
    "text_nums.loc['4.3'] = f'participation rate from {table1[\"export_partic_0407\"].min():.0f}'\n",
    "text_nums.loc['4.4'] = f'to {table1[\"export_partic_0407\"].max():.0f} percent,'\n",
    "text_nums.loc['4.5'] = f'the exit rate from {table1[\"exit_rate_0407\"].min():.0f} percent'\n",
    "text_nums.loc['4.6'] = f'to {table1[\"exit_rate_0407\"].max():.0f} percent, and'\n",
    "text_nums.loc['4.7'] = f'incumbent premium from {table1[\"incumbent_premium_0407\"].min():.2f} percent, and'\n",
    "text_nums.loc['4.8'] = f'to {table1[\"incumbent_premium_0407\"].max():.2f}.'\n",
    "text_nums.loc['4.9'] = f'(correlation {firm_moments[[\"gap_med\",\"export_partic_0407\"]].corr().iloc[0,1]:.2f}) but'\n",
    "text_nums.loc['4.10'] = f'exit rate ({firm_moments[[\"gap_med\",\"exit_rate_0407\"]].corr().iloc[0,1]:.2f}),'\n",
    "text_nums.loc['4.11'] = f'log exports ({firm_moments[[\"gap_med\",\"cov_lexports_0407\"]].corr().iloc[0,1]:.2f}),'\n",
    "text_nums.loc['4.12'] = f'incumbent premium ({firm_moments[[\"gap_med\",\"incumbent_premium_0407\"]].corr().iloc[0,1]:.2f}).'\n",
    "\n",
    "elas = pd.read_stata(dir_eint+'inputs_calibration_sectors.dta', columns=['sector_n', 'delast_S18_usachn'])\n",
    "elas = elas.loc[0:14]\n",
    "\n",
    "text_nums.loc['4.13'] = f'which range from {elas[\"delast_S18_usachn\"].min():.1f} to'\n",
    "text_nums.loc['4.14'] = f'to {elas[\"delast_S18_usachn\"].max():.1f}.'\n",
    "\n",
    "print(text_nums.loc[['4.1', '4.2','4.3','4.4','4.5','4.6','4.7','4.8','4.9','4.10','4.11','4.12','4.13','4.14']])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "860d496f",
   "metadata": {},
   "source": [
    "# Table 2\n",
    "\n",
    "Table 2 is created in the code in `11 Code structural model`. Here we adjust the formatting."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "00b50f35",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\\begin{tabular}{llccccc}\n",
      "\\toprule\n",
      "\\multicolumn{2}{c}{Sector} & $\\theta_g$ & $f_{g0}$ & $f_{g1}$ & $\\xi_{gH}$ & $\\sigma_{gz}$\\\\\n",
      "\\midrule\n",
      "1 & Food, beverage, tobacco & 3.09 & 0.14 & 0.12 & 3.34 & 0.91\\\\\n",
      "2 & Textile, clothing, footwear & 3.17 & 0.20 & 0.13 & 2.57 & 1.02\\\\\n",
      "3 & Wood and straw products & 2.79 & 0.26 & 0.17 & 3.71 & 1.03\\\\\n",
      "4 & Paper, printing products & 3.43 & 0.19 & 0.16 & 3.46 & 1.08\\\\\n",
      "5 & Energy products, chemicals & 2.99 & 0.27 & 0.20 & 4.56 & 1.17\\\\\n",
      "6 & Rubber, plastic products & 3.16 & 0.18 & 0.12 & 3.40 & 0.99\\\\\n",
      "7 & Non-metallic mineral products & 2.85 & 0.16 & 0.14 & 3.56 & 0.90\\\\\n",
      "8 & Base metal manuf. & 3.04 & 0.13 & 0.15 & 4.60 & 0.99\\\\\n",
      "9 & Calendered metal manuf. & 2.73 & 0.29 & 0.17 & 4.62 & 1.07\\\\\n",
      "10 & Other machinery, equipment & 3.74 & 0.23 & 0.15 & 3.03 & 1.20\\\\\n",
      "11 & Computer, electronic, optical & 3.18 & 0.46 & 0.20 & 4.81 & 1.35\\\\\n",
      "12 & Electrical equipment manuf. & 3.27 & 0.30 & 0.16 & 3.86 & 1.20\\\\\n",
      "13 & Vehicle manuf. & 3.06 & 0.20 & 0.15 & 4.81 & 1.07\\\\\n",
      "14 & Furniture, other manuf. & 3.26 & 0.20 & 0.11 & 2.25 & 0.98\\\\\n",
      "15 & Non-manufacturing & 2.97 & 0.22 & 0.16 & 4.04 & 1.07\\\\\n",
      "\\bottomrule\n",
      "\\addlinespace\n",
      "\\end{tabular}\n"
     ]
    }
   ],
   "source": [
    "with open('../11 Code structural model/scripts/tex/sector_params.tex', 'r') as f:\n",
    "    t2_l = f.readlines()\n",
    "\n",
    "t2_l = [r.strip() for r in t2_l]\n",
    "t2_l = t2_l[6:-3]\n",
    "t2_l.insert(0, '\\\\begin{tabular}{llccccc}')\n",
    "t2_l.append('\\\\addlinespace')\n",
    "t2_l.append('\\\\end{tabular}')\n",
    "\n",
    "t2_ltx = '\\n'.join(t2_l)\n",
    "print(t2_ltx)\n",
    "\n",
    "with open(dir_out+'table2.tex', 'w') as f:\n",
    "    f.write(t2_ltx)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a72406de",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "d2e79afd",
   "metadata": {},
   "source": [
    "# Appendix Table G1\n",
    "\n",
    "We refer to numbers from this table in the text, so the table is reproduced here. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "aef6babb",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\kimru\\AppData\\Local\\Temp\\ipykernel_36092\\3467696242.py:2: UnicodeWarning: \n",
      "One or more strings in the dta file could not be decoded using utf-8, and\n",
      "so the fallback encoding of latin-1 is being used.  This can happen when a file\n",
      "has been incorrectly encoded by Stata or some other software. You should verify\n",
      "the string values returned are correct.\n",
      "  sitc = pd.read_stata(dir_eint+'dataset_sitc.dta', columns=cols)\n"
     ]
    }
   ],
   "source": [
    "cols = ['year', 'cty', 'sitc1', 'v_jst', 'phase', 'nntr_med', 'chn', 'tariff_jst', 'ntr', 'fta', 'sitc']\n",
    "sitc = pd.read_stata(dir_eint+'dataset_sitc.dta', columns=cols)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "3e32ff89",
   "metadata": {},
   "outputs": [],
   "source": [
    "# NNTR rates for China\n",
    "nntr_med = sitc.query('year==1979 & phase==0 & chn==1').groupby('sitc1')['nntr_med'].agg(['mean', 'std'])\n",
    "nntr_all = sitc.query('year==1979 & phase==0 & chn==1')['nntr_med'].agg(['mean', 'std'])\n",
    "\n",
    "# Total Chinese exports to USA\n",
    "chtot = sitc[(sitc['phase']==0)&(sitc['chn']==1)].groupby('year')['v_jst'].sum()\n",
    "\n",
    "# Chinese share to USA by good\n",
    "share79 = sitc.query('year==1979 & phase==0 & chn==1').groupby('sitc1')['v_jst'].sum() / chtot[1979]\n",
    "share01 = sitc.query('year==2001 & phase==0 & chn==1').groupby('sitc1')['v_jst'].sum() / chtot[2001]\n",
    "\n",
    "# Applied duties\n",
    "adch79 = sitc.query('year==1979 & phase==0 & chn==1').groupby('sitc1')['tariff_jst'].agg(['mean'])\n",
    "adch01 = sitc.query('year==2001 & phase==0 & chn==1').groupby('sitc1')['tariff_jst'].agg(['mean'])\n",
    "\n",
    "adch79_all = sitc.query('year==1979 & phase==0 & chn==1')['tariff_jst'].agg(['mean']).values[0]\n",
    "adch01_all = sitc.query('year==2001 & phase==0 & chn==1')['tariff_jst'].agg(['mean']).values[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b878f200",
   "metadata": {},
   "source": [
    "We compute the NTR duties from the applied duties only for the goods that China exported\n",
    "to the United States. This is the `china_export` variable. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "14685dfb",
   "metadata": {},
   "outputs": [],
   "source": [
    "china_export = sitc.groupby(['sitc', 'year']).agg(china_export=('chn','max'))\n",
    "sitc = pd.merge(left=sitc, right=china_export, on=['sitc', 'year'], how='left')\n",
    "sitc.head(2)\n",
    "\n",
    "# NTR applied duties\n",
    "ntr79 = sitc.query('year==1979 & chn==0 & ntr==1 & fta==0 & phase==0 & china_export==True').groupby('sitc1')['tariff_jst'].agg(['mean'])\n",
    "ntr01 = sitc.query('year==2001 & chn==0 & ntr==1 & fta==0 & phase==0 & china_export==True').groupby('sitc1')['tariff_jst'].agg(['mean'])\n",
    "\n",
    "ntr79_all = sitc.query('year==1979 & chn==0 & ntr==1 & fta==0 & phase==0 & china_export==True')['tariff_jst'].agg(['mean']).values[0]\n",
    "ntr01_all = sitc.query('year==2001 & chn==0 & ntr==1 & fta==0 & phase==0 & china_export==True')['tariff_jst'].agg(['mean']).values[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "7d0a6d52",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>description</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>v_jst</th>\n",
       "      <th>v_jst</th>\n",
       "      <th>mean</th>\n",
       "      <th>mean</th>\n",
       "      <th>mean</th>\n",
       "      <th>mean</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Food and live animals</td>\n",
       "      <td>16.434830</td>\n",
       "      <td>14.355257</td>\n",
       "      <td>13.306537</td>\n",
       "      <td>1.322674</td>\n",
       "      <td>15.173696</td>\n",
       "      <td>4.247834</td>\n",
       "      <td>3.199015</td>\n",
       "      <td>2.417410</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Beverages and tobacco</td>\n",
       "      <td>48.268623</td>\n",
       "      <td>45.409630</td>\n",
       "      <td>0.177323</td>\n",
       "      <td>0.040538</td>\n",
       "      <td>48.734261</td>\n",
       "      <td>13.729079</td>\n",
       "      <td>2.743196</td>\n",
       "      <td>2.357694</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Crude materials, inedible, except fuels</td>\n",
       "      <td>10.987041</td>\n",
       "      <td>17.133224</td>\n",
       "      <td>16.988779</td>\n",
       "      <td>0.682884</td>\n",
       "      <td>6.317703</td>\n",
       "      <td>1.174094</td>\n",
       "      <td>0.325085</td>\n",
       "      <td>0.199128</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Mineral fuels, lubricants and related</td>\n",
       "      <td>1.285408</td>\n",
       "      <td>1.421912</td>\n",
       "      <td>25.060194</td>\n",
       "      <td>0.459007</td>\n",
       "      <td>1.326604</td>\n",
       "      <td>0.134466</td>\n",
       "      <td>0.627662</td>\n",
       "      <td>0.401756</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Animal and vegetable oils, fats and waxes</td>\n",
       "      <td>11.096344</td>\n",
       "      <td>6.703624</td>\n",
       "      <td>0.891928</td>\n",
       "      <td>0.006716</td>\n",
       "      <td>3.950397</td>\n",
       "      <td>1.907562</td>\n",
       "      <td>1.907932</td>\n",
       "      <td>1.644452</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Chemical and related products</td>\n",
       "      <td>28.943741</td>\n",
       "      <td>15.433773</td>\n",
       "      <td>13.861083</td>\n",
       "      <td>2.293158</td>\n",
       "      <td>19.846861</td>\n",
       "      <td>4.699039</td>\n",
       "      <td>3.004462</td>\n",
       "      <td>2.270150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Manufactured goods</td>\n",
       "      <td>36.449162</td>\n",
       "      <td>17.177214</td>\n",
       "      <td>11.552607</td>\n",
       "      <td>10.403462</td>\n",
       "      <td>38.235538</td>\n",
       "      <td>7.228767</td>\n",
       "      <td>2.287309</td>\n",
       "      <td>1.633396</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Machinery and transport equip.</td>\n",
       "      <td>33.998016</td>\n",
       "      <td>10.752109</td>\n",
       "      <td>0.282092</td>\n",
       "      <td>39.879019</td>\n",
       "      <td>32.601025</td>\n",
       "      <td>5.393046</td>\n",
       "      <td>1.420884</td>\n",
       "      <td>0.958929</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Misc. manufactured articles</td>\n",
       "      <td>39.182312</td>\n",
       "      <td>23.174931</td>\n",
       "      <td>16.715970</td>\n",
       "      <td>44.894662</td>\n",
       "      <td>40.351650</td>\n",
       "      <td>7.176887</td>\n",
       "      <td>2.994541</td>\n",
       "      <td>1.858771</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Commodities and transactions, n.e.c.</td>\n",
       "      <td>33.333336</td>\n",
       "      <td>23.570227</td>\n",
       "      <td>1.163488</td>\n",
       "      <td>0.017880</td>\n",
       "      <td>28.070656</td>\n",
       "      <td>2.628412</td>\n",
       "      <td>0.697638</td>\n",
       "      <td>0.617348</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Average</td>\n",
       "      <td>29.027328</td>\n",
       "      <td>21.109270</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>28.101206</td>\n",
       "      <td>5.925564</td>\n",
       "      <td>2.165487</td>\n",
       "      <td>1.543570</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  description       mean        std  \\\n",
       "0                       Food and live animals  16.434830  14.355257   \n",
       "1                       Beverages and tobacco  48.268623  45.409630   \n",
       "2     Crude materials, inedible, except fuels  10.987041  17.133224   \n",
       "3       Mineral fuels, lubricants and related   1.285408   1.421912   \n",
       "4   Animal and vegetable oils, fats and waxes  11.096344   6.703624   \n",
       "5               Chemical and related products  28.943741  15.433773   \n",
       "6                          Manufactured goods  36.449162  17.177214   \n",
       "7              Machinery and transport equip.  33.998016  10.752109   \n",
       "8                 Misc. manufactured articles  39.182312  23.174931   \n",
       "9        Commodities and transactions, n.e.c.  33.333336  23.570227   \n",
       "10                                    Average  29.027328  21.109270   \n",
       "\n",
       "        v_jst      v_jst       mean       mean      mean      mean  \n",
       "0   13.306537   1.322674  15.173696   4.247834  3.199015  2.417410  \n",
       "1    0.177323   0.040538  48.734261  13.729079  2.743196  2.357694  \n",
       "2   16.988779   0.682884   6.317703   1.174094  0.325085  0.199128  \n",
       "3   25.060194   0.459007   1.326604   0.134466  0.627662  0.401756  \n",
       "4    0.891928   0.006716   3.950397   1.907562  1.907932  1.644452  \n",
       "5   13.861083   2.293158  19.846861   4.699039  3.004462  2.270150  \n",
       "6   11.552607  10.403462  38.235538   7.228767  2.287309  1.633396  \n",
       "7    0.282092  39.879019  32.601025   5.393046  1.420884  0.958929  \n",
       "8   16.715970  44.894662  40.351650   7.176887  2.994541  1.858771  \n",
       "9    1.163488   0.017880  28.070656   2.628412  0.697638  0.617348  \n",
       "10        NaN        NaN  28.101206   5.925564  2.165487  1.543570  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sitc1_dic = {'0':'Food and live animals',\n",
    "              '1':'Beverages and tobacco',\n",
    "              '2':'Crude materials, inedible, except fuels',\n",
    "              '3':'Mineral fuels, lubricants and related',\n",
    "              '4':'Animal and vegetable oils, fats and waxes',\n",
    "              '5':'Chemical and related products',\n",
    "              '6':'Manufactured goods',\n",
    "              '7':'Machinery and transport equip.',\n",
    "              '8':'Misc. manufactured articles',\n",
    "              '9':'Commodities and transactions, n.e.c.',\n",
    "              }\n",
    "desc_sitc1 = pd.Series(data=sitc1_dic, name='description')\n",
    "\n",
    "ta1 = pd.concat([ nntr_med, share79, share01, adch79, ntr79, adch01, ntr01], axis=1)\n",
    "ta1 = ta1*100\n",
    "ta1 = pd.concat([desc_sitc1, ta1], axis=1)\n",
    "\n",
    "ta1.loc[10] = ['Average', nntr_all.values[0]*100, nntr_all.values[1]*100, np.nan, np.nan, adch79_all*100, ntr79_all*100, adch01_all*100, ntr01_all*100]\n",
    "ta1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "f3a73769",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\\begin{tabular}{clcccccccc}\n",
      "\\toprule\n",
      "&&&&&&\\multicolumn{4}{c}{Applied Duties}\\\\\n",
      "\\cmidrule(lr){7-10}\n",
      "&&\\multicolumn{2}{c}{NNTR Rate}&\\multicolumn{2}{c}{U.S. Export Share}&\\multicolumn{2}{c}{1979}&\\multicolumn{2}{c}{2001}\\\\\n",
      "\\cmidrule(lr){3-4} \\cmidrule(lr){5-6} \\cmidrule(lr){7-8} \\cmidrule(lr){9-10}\n",
      "&SITC 1-Digit & Mean & Std. & 1979 & 2001 & China & NTR & China & NTR\\\\\n",
      "\\midrule\n",
      "0 & Food and live animals & 16 & 14 & 13 & 1 & 15 & 4 & 3 & 2 \\\\\n",
      "1 & Beverages and tobacco & 48 & 45 & 0 & 0 & 49 & 14 & 3 & 2 \\\\\n",
      "2 & Crude materials, inedible, except fuels & 11 & 17 & 17 & 1 & 6 & 1 & 0 & 0 \\\\\n",
      "3 & Mineral fuels, lubricants and related & 1 & 1 & 25 & 0 & 1 & 0 & 1 & 0 \\\\\n",
      "4 & Animal and vegetable oils, fats and waxes & 11 & 7 & 1 & 0 & 4 & 2 & 2 & 2 \\\\\n",
      "5 & Chemical and related products & 29 & 15 & 14 & 2 & 20 & 5 & 3 & 2 \\\\\n",
      "6 & Manufactured goods & 36 & 17 & 12 & 10 & 38 & 7 & 2 & 2 \\\\\n",
      "7 & Machinery and transport equip. & 34 & 11 & 0 & 40 & 33 & 5 & 1 & 1 \\\\\n",
      "8 & Misc. manufactured articles & 39 & 23 & 17 & 45 & 40 & 7 & 3 & 2 \\\\\n",
      "9 & Commodities and transactions, n.e.c. & 33 & 24 & 1 & 0 & 28 & 3 & 1 & 1 \\\\\n",
      "\\midrule\n",
      "10 & Average & 29 & 21 &  &  & 28 & 6 & 2 & 2 \\\\\n",
      "\\bottomrule\n",
      "\\end{tabular}\n"
     ]
    }
   ],
   "source": [
    "ta1_ltx = ta1.to_latex(header=False, na_rep='', float_format='%.0f', column_format='clcccccccc')\n",
    "# split it into a list to format\n",
    "ta_l = ta1_ltx.splitlines()\n",
    "ta_l.insert(2, '&&&&&&\\\\multicolumn{4}{c}{Applied Duties}\\\\\\\\')\n",
    "ta_l.insert(3, '\\\\cmidrule(lr){7-10}')\n",
    "ta_l.insert(4, '&&\\\\multicolumn{2}{c}{NNTR Rate}&\\\\multicolumn{2}{c}{U.S. Export Share}&\\\\multicolumn{2}{c}{1979}&\\\\multicolumn{2}{c}{2001}\\\\\\\\')\n",
    "ta_l.insert(5, '\\\\cmidrule(lr){3-4} \\\\cmidrule(lr){5-6} \\\\cmidrule(lr){7-8} \\\\cmidrule(lr){9-10}')\n",
    "ta_l.insert(6, '&SITC 1-Digit & Mean & Std. & 1979 & 2001 & China & NTR & China & NTR\\\\\\\\')\n",
    "ta_l.insert(-3, '\\\\midrule')\n",
    "\n",
    "ta1_ltx = '\\n'.join(ta_l)\n",
    "print(ta1_ltx)\n",
    "\n",
    "with open(dir_outa+'tableG1.tex', 'w') as f:\n",
    "     f.write(ta1_ltx)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "46759446",
   "metadata": {},
   "source": [
    "## Numbers in the text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "951c117d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                                text\n",
      "pos                                 \n",
      "2.2                  NNTR rate is 29\n",
      "2.3  with a standard deviation of 21\n",
      "2.4      NTR countries was 6 in 1979\n",
      "2.5                    and 2 in 2001\n",
      "2.6                  in 1979 (28 vs.\n",
      "2.7                          vs. 6),\n",
      "2.8                   by 2001 (2 vs.\n",
      "2.9                          vs. 2).\n"
     ]
    }
   ],
   "source": [
    "text_nums.loc['2.2', 'text'] = f'NNTR rate is {nntr_all.values[0]*100:.0f}'\n",
    "text_nums.loc['2.3', 'text'] = f'with a standard deviation of {nntr_all.values[1]*100:.0f}'\n",
    "text_nums.loc['2.4'] = f'NTR countries was {ntr79_all*100:.0f} in 1979'\n",
    "text_nums.loc['2.5'] = f'and {ntr01_all*100:.0f} in 2001'\n",
    "text_nums.loc['2.6'] = f'in 1979 ({adch79_all*100:.0f} vs.'\n",
    "text_nums.loc['2.7'] = f'vs. {ntr79_all*100:.0f}),'\n",
    "text_nums.loc['2.8'] = f'by 2001 ({adch01_all*100:.0f} vs.'\n",
    "text_nums.loc['2.9'] = f'vs. {ntr01_all*100:.0f}).'\n",
    "\n",
    "print(text_nums.loc[['2.2', '2.3', '2.4', '2.5', '2.6', '2.7', '2.8', '2.9']])\n",
    "text_nums.to_csv(dir_out+'numbers for paper text.csv')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "newest",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.19"
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
